package sf.database.dialect.db;

import sf.core.DBField;
import sf.database.dbinfo.Feature;
import sf.database.dialect.DBDialect;
import sf.database.dialect.DBProperty;
import sf.database.dialect.Keywords;
import sf.database.meta.ColumnMapping;
import sf.database.meta.TableMapping;
import sf.database.support.DBMS;
import sf.database.util.DBUtils;
import sf.jooq.JooqSupportDatabase;
import sf.querydsl.QueryDSLSupportDatabase;
import sf.tools.JavaTypeUtils;
import sf.tools.StringUtils;
import sf.tools.SystemUtils;

import javax.persistence.PersistenceException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

/**
 * postgresql
 */
public class PostgreSqlDialect extends DBDialect {
    @Override
    public String getName() {
        return DBMS.postgresql.name();
    }

    @Override
    public int getNumber() {
        return DBMS.postgresql.getNumber();
    }

    public PostgreSqlDialect() {
        this.keywords.addAll(Keywords.POSTGRESQL);
        features.addAll(Arrays.asList(Feature.ALTER_FOR_EACH_COLUMN, Feature.COLUMN_ALTERATION_SYNTAX, Feature.SUPPORT_CONCAT, Feature.SUPPORT_SEQUENCE, Feature.SUPPORT_LIMIT, Feature.AI_TO_SEQUENCE_WITHOUT_DEFAULT,
                Feature.SUPPORT_COMMENT));
        setProperty(DBProperty.ADD_COLUMN, "ADD COLUMN");
        setProperty(DBProperty.MODIFY_COLUMN, "ALTER COLUMN");
        setProperty(DBProperty.DROP_COLUMN, "DROP COLUMN");
        setProperty(DBProperty.CHECK_SQL, "select 1");
        setProperty(DBProperty.SEQUENCE_FETCH, "select nextval('%s')");
        setProperty(DBProperty.WRAP_FOR_KEYWORD, "\"\"");
        setProperty(DBProperty.GET_IDENTITY_FUNCTION, "SELECT currval('%tableName%_%columnName%_seq')");
        setProperty(DBProperty.NVL_FUNCTION, "coalesce(%1$s,%2$s)");
    }

    @Override
    public String getDefaultSchema() {
        return "public";
    }

    public String evalFieldType(ColumnMapping mf) {
        String type = "";
        int length = getColumnLength(mf);
        int scale = getColumnScale(mf);
        int precision = getColumnPrecision(mf);
        switch (mf.getSqlType()) {
            case Types.VARCHAR: {
                type = "VARCHAR(" + length + ")";
                break;
            }
            case Types.TINYINT: {
                type = "SMALLINT";
                break;
            }
            case Types.SMALLINT: {
                type = "SMALLINT";
                break;
            }
            case Types.INTEGER: {
                type = "INT";
                break;
            }
            case Types.BIGINT: {
                type = "BIGINT";
                break;
            }
            //使用默认的
//            case Types.FLOAT: {
//                type = "real";
//                break;
//            }
//            case Types.DOUBLE:{
//                type = "double precision";
//                break;
//            }
            case Types.BOOLEAN: {
                type = "BOOLEAN";
                break;
            }
            case Types.DECIMAL: {
                // BigDecimal
                if (scale > 0 && precision > 0) {
                    type = "DECIMAL(" + precision + "," + scale + ")";
                } else {
                    //postgresql支持无限制
                    type = "DECIMAL";
                }
                break;
            }
            case Types.NUMERIC: {
                if (precision > 0) {
                    type = "NUMERIC(" + precision + "," + scale + ")";
                } else {
                    //postgresql支持无限制
                    type = "NUMERIC";
                }
                break;
            }
            case Types.DATE:
                type = "DATE";
                break;
            case Types.TIME:
                type = "TIME";
                break;
            case Types.TIME_WITH_TIMEZONE:
                type = "TIME WITH TIME ZONE";
                break;
            case Types.TIMESTAMP:
                type = "TIMESTAMP";
                break;
            case Types.TIMESTAMP_WITH_TIMEZONE:
                type = "TIMESTAMP WITH TIME ZONE";
                break;
            case Types.BLOB:
            case Types.BINARY:
            case Types.VARBINARY:
            case Types.LONGVARBINARY:
                type = "BYTEA";
                break;
            case Types.CLOB:
            case Types.LONGVARCHAR:
            case Types.LONGNVARCHAR: {
                type = "TEXT";
                break;
            }
            default:
                type = super.evalFieldType(mf);
                break;
        }
        return type;
    }

    @Override
    public boolean createEntity(Connection conn, TableMapping en) {
        StringBuilder sb = new StringBuilder("CREATE TABLE " + wrapKeyword(en.getTableName()) + "(");
        // 创建字段
        boolean delimiter = false;
        for (Map.Entry<DBField, ColumnMapping> entry : en.getSchemaMap().entrySet()) {
            ColumnMapping cm = entry.getValue();
            if (cm.getJpaTransient() != null) {
                continue;
            }
            sb.append(delimiter ? "," : "").append(SystemUtils.lineSeparator);
            sb.append(wrapKeyword(cm.getRawColumnName()));
            // 自增主键特殊形式关键字
            if (cm.isPk() && cm.getGv() != null && JavaTypeUtils.isNumberClass(cm.getClz())) {
                if (JavaTypeUtils.isLong(cm.getClz())){
                    sb.append(" BIGSERIAL");
                }else {
                    sb.append(" SERIAL");
                }
            } else {
                // 普通字段
                sb.append(' ').append(evalFieldDefinition(cm));
            }
            delimiter = true;
        }
        // 创建主键
        List<ColumnMapping> pks = en.getPkFields();
        if (!pks.isEmpty()) {
            sb.append(',').append(SystemUtils.lineSeparator);
            sb.append(String.format("CONSTRAINT pk_%s PRIMARY KEY (",
                    en.getTableName().replace('.', '_').replace('"', '_')));
            boolean f = false;
            for (ColumnMapping pk : pks) {
                sb.append(f ? "," : "").append(wrapKeyword(pk.getRawColumnName()));
                f = true;
            }
            sb.append(") ").append(SystemUtils.lineSeparator);
            sb.append(SystemUtils.lineSeparator).append(" ");
        }

        // 结束表字段设置
        sb.append(')');
        // 设置特殊引擎
//		if (en.hasMeta(META_ENGINE)) {
//			sb.append(" ENGINE=" + en.getMeta(META_ENGINE));
//		}
        // 默认采用 UTF-8 编码

        sb.append(" ");
        // 表名注释

        List<String> sqls = new ArrayList<>();
        // 执行创建语句
        sqls.add(sb.toString());
        // 创建索引
        sqls.addAll(createIndexSql(en));
        //创建约束
        sqls.addAll(createUniqueSql(en));
        // 添加注释(表注释与字段注释)
        sqls.addAll(addCommentSql(en));

        execute(sqls, conn);

        // 创建关联表
        createRelation(conn, en);


        return true;
    }

    @Override
    public String uniqueSql(String tableName, String name, String[] columnNames) {
        StringBuilder sb = new StringBuilder();
        sb.append("alter table ").append(wrapKeyword(tableName)).append(" add constraint ").append(name).append(" unique(");
        for (int i = 0; i < columnNames.length; i++) {
            String column = columnNames[i];
            sb.append(i > 0 ? "," : "").append(wrapKeyword(column));
        }
        sb.append(")");
        return sb.toString();
    }

    @Override
    protected String escapeSqlValue(String value) {
        return StringUtils.replace(value, "'", "\\'");
    }

    @Override
    public String sqlTableDrop(String table) {
        return String.format("drop table if exists %s;", wrapKeyword(table));
    }

    @Override
    public String sqlTableRename(String oldName, String newName) {
        return String.format("rename table  %s to %s;", wrapKeyword(oldName), wrapKeyword(newName));
    }

    @Override
    public String sqlColumnRename(String table, String oldColumnName, String newColumnName) {
        return null;
    }

    @Override
    public String sqlColumnAdd(String table, String column_definition, String column_position) {
        String sql = String.format("alter table %s add %s", wrapKeyword(table), column_definition);
        if (supportsColumnPosition() && column_position != null) {
            sql = sql + " " + column_position;
        }
        return sql + ";";
    }

    @Override
    public String sqlColumnModify(String table, String column_definition, String column_position) {
        String sql = String.format("alter table %s modify %s", wrapKeyword(table), column_definition);
        if (supportsColumnPosition() && column_position != null) {
            sql = sql + " " + column_position;
        }
        return sql + ";";
    }

    @Override
    public String sqlColumnDrop(String table, String column) {
        return String.format("alter table %s drop %s;", wrapKeyword(table), wrapKeyword(column));
    }

    @Override
    public StringBuilder sqlPageList(StringBuilder sql, long offset, int limit) {
        if (offset > 0) {
            return sql.append(" limit ").append(limit).append(" offset ").append(offset);
        } else {
            return sql.append(" limit ").append(limit);
        }
    }

    @Override
    public boolean supportsColumnPosition() {
        return true;
    }

    @Override
    public long getColumnAutoIncreamentValue(ColumnMapping mapping, Connection conn) {
        String tableName = mapping.getMeta().getTableName().toLowerCase();
        String seqname = tableName + "_" + mapping.getLowerColumnName() + "_seq";
        String sql = String.format("select nextval('%s')", seqname);
        try {
            Statement st = conn.createStatement();
            ResultSet rs = null;
            try {
                rs = st.executeQuery(sql);
                rs.next();
                return rs.getLong(1);
            } finally {
                DBUtils.closeQuietly(rs);
                DBUtils.closeQuietly(st);
            }
        } catch (SQLException e) {
            throw new PersistenceException(e);
        }
    }

    @Override
    public String getHibernateDialect() {
        return "org.hibernate.dialect.PostgreSQL94Dialect";
    }

    public String getSeqNextValSql(String seqName) {
        return "nextval(\"" + seqName + "\")";
    }

    @Override
    public String getQueryDslDialect() {
        return QueryDSLSupportDatabase.PostgreSQL;
    }

    @Override
    public String getJooqDialect() {
        return JooqSupportDatabase.POSTGRES;
    }
}
